<?php

class DB_Table {

	/**
	 * _dbh
	 * DB对象的实例
	 *
	 * @var DB
	 */
	private $_dbh;

	/**
	 * _dsn
	 * dsn字符串
	 *
	 * @var mixed
	 */
	private $_dsn;

	/**
	 * _table
	 * 当前操作的物理表名
	 *
	 * @var string
	 */
	private $_table;

	/**
	 * 构建数据操作实例
	 *
	 * - 由外部指定分表时(尚不支持)：
	 *   $db = new DB_Table('table_name_1');
	 *
	 * - 由外部指定不分表时
	 *   $db = new DB_Table('table_name');
	 *
	 * - 本程序负责分表时：
	 *   $db = new DB_Table('table_name', array('s_id' => 1000));
	 *
	 * @param string $table
	 * @param array $shardKey 分库标志
	 *  + key 数据库字段
	 *  + value 与字段对应的值
	 * @throws Exception
	 */
	public function __construct($table, $shardKey = array()) {
		$conf = self::shardTable($table, $shardKey);
		$this->_table = $conf['table'];

		if (!array_key_exists('charset', $conf['db']) || !$conf['db']['charset']) {
			$conf['db']['charset'] = 'utf8';
		}

		$this->_dsn = $conf['db']['dsn'];
		$this->_dbh = DB::connect($conf['db']['dsn'],
			$conf['db']['user'],
			$conf['db']['password'],
			$conf['db']['charset'],
			$conf['db']['failover'] ?? '',
			$conf['db']['persistent'] ?? '',
			$conf['db']['timeout'] ?? 0);
	}

	/**
	 * getDbh
	 * 获取DB对象
	 *
	 * @return DB
	 */
	public function getDbh() {
		return $this->_dbh;
	}

	/**
	 * 根据表名和key分库(分表)
	 *
	 * 目前shardKey只支持一个字段
	 *
	 * 根据表名前缀(第一个_前面的部分)区分是哪个模块，进而读取相应模块的数据库配置文件，
	 * 如果需要根据模块和$key分数据库，则返回相应的配置；否则使用默认的配置
	 * 在配置文件conf/db.php中，指定$conf['MODULE.siteTables.TABLE.num']的值，可实现所有数据库上TABLE的分表
	 *
	 * @param string $table 表名
	 * @param array $shardKey 切分关键字
	 * @return array
	 *           + db 数据库相关配置
	 *           + table 物理表名
	 * @throws Exception
	 */
	public static function shardTable($table, $shardKey = array()) {

		$dbConfig = Sdk\lib\Config::GetApp('db');
		if (!$dbConfig) {
			$dbConfig = Sdk\lib\Config::GetRoot('db');
		}
		$result = array();

		$shardConfig = isset($dbConfig[$table]['shard']) ? $dbConfig[$table]['shard'] : null;

		// 分库分表
		if ($shardConfig) {
			$shardConfig['table'] = $table;
			$shard = DB_Shard::factory($shardConfig, $shardKey);
			$result['db'] = $shard->getDB();
			$result['table'] = $table;

			return $result;
		}

		// 不分库的情况
		$result['db'] = $dbConfig;
		$result['table'] = $table;
		return $result;
	}

	/**
	 * 获取一条记录
	 *
	 * @param string|null $conditions
	 * @param array $params
	 * @param string[] $columns
	 * @return mixed
	 * @throws Exception
	 */
	public function find($conditions = null, $params = array(), $columns = array('*')) {
		if (!$columns) {
			$columns = '*';
		} else {
			$columns = implode(',', $columns);
		}
		$sql = "SELECT $columns FROM $this->_table";

		if ($conditions) {
			$sql .= ' WHERE ' . $conditions;
		}

		try {
			$data = $this->_dbh->fetchRow($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $data;
	}

	/**
	 * 取得所有的记录
	 *
	 * @param string|null $conditions
	 * @param array $params
	 * @param array $columns
	 * @param integer $start
	 * @param integer $limit
	 * @param array $order
	 *   fieldName1 => [ASC|DESC]
	 *   fieldName2 => [ASC|DESC]
	 * @return array
	 * @throws Exception
	 */
	public function findAll($conditions = null, $params = array(), $columns = array('*'), $start = 0, $limit = 0, $order = array()) {
		if (!$columns) {
			$columns = '*';
		} else {
			$columns = implode(',', $columns);
		}
		$sql = "SELECT $columns FROM $this->_table ";

		if ($conditions) {
			$sql .= ' WHERE ' . $conditions;
		}

		if ($order && is_array($order)) {
			$orderClause = '';
			foreach ($order as $field => $orderBy) {
				$orderClause .= $field . ' ' . $orderBy . ',';
			}
			$sql .= ' ORDER BY ' . rtrim($orderClause, ',');
		} else if ($order && is_string($order)) {
			$sql .= ' ORDER BY ' . $order;
		}

		if ($limit) {
			$sql .= " LIMIT $start, $limit";
		}

		try {
			$rows = $this->_dbh->fetchAll($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $rows;
	}

	/**
	 * findBySql
	 *
	 * @param string $sql
	 * @param array $params
	 * @return array
	 * @throws Exception
	 */
	public function findBySql($sql, $params = array()) {
		try {
			$sql = $this->_rewriteSql($sql);
			$data = $this->_dbh->fetchAll($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $data;
	}

	/**
	 * 删除记录
	 *
	 * @param string $conditions
	 * @param array $params
	 * @return bool
	 * @throws Exception
	 */
	public function remove($conditions, $params) {
		$sql = "DELETE FROM $this->_table WHERE $conditions ";

		try {
			$result = $this->_dbh->exec($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * 插入一条记录 或更新表记录
	 *
	 * @param array $data
	 * @param string|null $conditions
	 * @param array $params
	 * @return bool
	 * @throws Exception
	 */
	public function save($data, $conditions = NULL, $params = array()) {
		$tempParams = $set = array();
		foreach ($data as $k => $v) {
			if (is_array($v)) {
				foreach ($v as $v2) {
					$set[] = $k . '= ' . $v2[0];
					if (count($v2) > 1) {
						foreach ($v2[1] as $_v) {
							$tempParams[] = $_v;
						}
					}
				}
			} else {
				$set[] = $k . '= ?';
				$tempParams[] = $v;
			}
		}

		if ($conditions) {
			// 更新
			$sql = "UPDATE $this->_table SET " . join(',', $set) . " WHERE $conditions";
			$params = array_merge($tempParams, $params);
		} else {
			// 插入
			$sql = "INSERT INTO  $this->_table SET " . join(',', $set);
			$params = $tempParams;
		}

		try {
			$result = $this->_dbh->exec($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * replace
	 * 根据主键替换或保存
	 *
	 * @param array $data
	 * @return bool
	 * @throws Exception
	 */
	public function replace($data) {
		$tempParams = $set = array();
		foreach ($data as $k => $v) {
			$set[] = $k . '= ?';
			$tempParams[] = $v;
		}

		$sql = "REPLACE INTO $this->_table SET " . join(',', $set);

		try {
			$result = $this->_dbh->exec($sql, $tempParams);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * 返回最后的 id
	 *
	 * @return string
	 * @throws Exception
	 */
	public function lastInsertId() {
		try {
			return $this->_dbh->lastInsertId();
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}
	}

	/**
	 * 一次插入多条记录
	 *
	 * @param  $data
	 * @return bool
	 * @throws Exception
	 */
	public function multiInsert($data) {
		$count = count($data);
		$getKeys = array_keys($data[0]);
		$countKeys = count($getKeys);
		$columns = implode(',', $getKeys);

		// 构造问号表达式
		$tmpArr = array();
		for ($i = 0; $i < $countKeys; $i++) {
			$tmpArr[] = '?';
		}
		$tmpStr = implode(',', $tmpArr);
		$tmpStr = '(' . $tmpStr . ')';
		$tmpArr2 = array();
		$mergeArr = array();
		for ($i = 0; $i < $count; $i++) {
			$tmpArr2[] = $tmpStr;
			$mergeArr = array_merge($mergeArr, array_values($data[$i]));
		}

		$tmpStr2 = implode(',', $tmpArr2);
		$conditions = "INSERT INTO $this->_table ($columns) VALUES $tmpStr2";

		try {
			$result = $this->_dbh->exec($conditions, $mergeArr);
		} catch (Exception  $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * 数据表列名到缓存列名的转换
	 *
	 * @param array $array
	 * @return array
	 */
	public function keyMap($array) {
		$outArray = array();
		foreach ($array as $key => $value) {
			$keyArr = explode('_', $key);
			$outKey = ucfirst($keyArr[1]) . ucfirst(@$keyArr[2]) . ucfirst(@$keyArr[3]);
			$outArray[$outKey] = $value;
		}
		return $outArray;
	}

	/**
	 * count
	 * 计算行数
	 *
	 * @param string|null $conditions
	 * @param array $params
	 * @return mixed
	 * @throws Exception
	 */
	public function count($conditions = NULL, $params = array()) {
		$sql = 'SELECT COUNT(*) FROM ' . $this->_table;

		try {
			if ($conditions) {
				$sql .= ' WHERE ' . $conditions;
			}
			$result = $this->_dbh->fetchOne($sql, $params);
		} catch (Exception  $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * exec
	 * 执行SQL
	 *
	 * @param string $sql
	 * @param array $params
	 * @return bool
	 * @throws Exception
	 */
	public function exec($sql, $params = array()) {
		try {
			$sql = $this->_rewriteSql($sql);
			$result = $this->_dbh->exec($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * explain
	 * 执行SQL explain
	 *
	 * @param string $sql
	 * @param array $params
	 * @return array
	 * @throws Exception
	 */
	public function explain($sql, $params = array()) {
		try {
			$result = $this->_dbh->fetchAll('EXPLAIN ' . $sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * incr
	 * 增加值
	 *
	 * @param string $field
	 * @param string|null $conditions
	 * @param array $params
	 * @param int $unit
	 * @return bool
	 * @throws Exception
	 */
	public function incr($field, $conditions = null, $params = array(), $unit = 1) {
		$sql = 'UPDATE ' . $this->_table . " SET `$field` = `$field` + $unit";
		if ($conditions) {
			$sql .= ' WHERE ' . $conditions;
		}

		try {
			$result = $this->_dbh->exec($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * decr
	 * 减少值
	 *
	 * @param string $field
	 * @param string|null $conditions
	 * @param array $params
	 * @param int $unit
	 * @return bool
	 * @throws Exception
	 */
	public function decr($field, $conditions = null, $params = array(), $unit = 1) {
		$sql = 'UPDATE ' . $this->_table . " SET $field = IF($field > $unit,  $field - $unit, 0)";
		if ($conditions) {
			$sql .= ' WHERE ' . $conditions;
		}

		try {
			$result = $this->_dbh->exec($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	/**
	 * truncate
	 * 清空
	 *
	 * @return bool
	 * @throws Exception
	 */
	public function truncate() {
		$sql = "TRUNCATE  $this->_table  ";
		$params = array();


		try {
			$result = $this->_dbh->exec($sql, $params);
		} catch (Exception $e) {
			throw new Exception($e->getMessage(), $e->getCode());
		}

		return $result;
	}

	public function beginTransaction() {
		$this->_dbh->begin();
	}

	public function commitTransaction() {
		$this->_dbh->commit();
	}

	public function rollBackTransaction() {
		$this->_dbh->rollBack();
	}

	public function setAttr($attr, $value) {
		return $this->_dbh->setAttr($attr, $value);
	}

	protected function _rewriteSql($sql) {
		$pattern = '/((?:select.*?from|insert into|delete from|update|replace into|truncate table|describe|alter table)\s+)`?(\w+)`?/i';
		return preg_replace($pattern, '\1' . $this->_table, $sql);
	}

	public function close() {
		$this->_dbh->close($this->_dsn);
	}

	public static function closeAll() {
		return DB::closeAll();
	}
}
